/**
 * DBConnection.java - written by Anders Mellerborg. Date created: 2009-may-19.
 * Encoding: UTF-8.
 */
package theserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JOptionPane;


/**
 * The database connection class. Manages all communication between the server
 * and the mySQL database.
 * 
 * @author <a href="mailto:and-mell@dsv.su.se">Anders Mellerborg</a>
 * @see DeviceInfo
 * @see DeviceList
 * @see VenueList
 * @see DriverManager
 * @see SQLException
 * @see Connection
 * @see Statement
 * @see PreparedStatement
 * @see ResultSet
 */
public class DBConnection {

	private Statement		stmt;
	private Connection		con;
	private final String	checkInStatement					= "INSERT INTO DEVICE(deviceID,owner,ip,port,lastseen, location) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE owner = VALUES(owner), ip = VALUES(ip), port = VALUES(port), lastseen = VALUES(lastseen), location = VALUES(location);";
	private final String	getAllDevicesStatement				= "SELECT deviceid, owner, ip, port, lastseen, location FROM DEVICE;";
	private final String	getAllVenuesAndActivitiesStatement	= "SELECT location,venuename, activitytype, minparticipants, maxparticipants FROM VENUE, VENUEACTIVITY, VENUELOCATION, ACTIVITYTYPE WHERE VENUELOCATION.venue = VENUE.venueid AND VENUE.venueid = VENUEACTIVITY.venue AND VENUEACTIVITY.activity = ACTIVITYTYPE.activityid AND location = ?;";
	private final String	getAllDevicesForLocationStatement	= "SELECT deviceid, owner, ip, port, lastseen FROM DEVICE WHERE location = ?;";
	private final String	getAllLocationsStatement			= "SELECT locationid FROM LOCATION;";
	private final String	createVenueStatement				= "INSERT INTO VENUE(venuename,venueaddress,venuezip,venuetown,venuephone) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE venuename = VALUES(venuename), venueaddress = VALUES(venueaddress), venuezip = VALUES(venuezip), venuetown = VALUES(venuetown), venuephone = VALUES(venuephone);";
	private final String	getLocationStatement				= "SELECT locationid FROM LOCATION WHERE bssid = ?";
	private final String	getIDsForDevice						= "SELECT bssid, ssid FROM LOCATION WHERE locationid = ?;";

	static {
		try {
			// Informs the class what type of database it's going to connect to
			// and by which Driver
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			JOptionPane.showMessageDialog(null,
					"JDBC fel: InstantiationException.", "Felmeddelande",
					JOptionPane.ERROR_MESSAGE);
		} catch (IllegalAccessException e) {
			JOptionPane.showMessageDialog(null,
					"JDBC fel: IllegalAccessException", "Felmeddelande",
					JOptionPane.ERROR_MESSAGE);
		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null,
					"JDBC fel: ClassNotFoundException.", "Felmeddelande",
					JOptionPane.ERROR_MESSAGE);
		}

	}


	private void displayErrorMessage(String errorMessage) {
		JOptionPane.showMessageDialog(null, errorMessage, "Felmeddelande",
				JOptionPane.ERROR_MESSAGE);
	}


	/**
	 * Constructor for the DBConnection. Creates a connection to the correct
	 * database at atlas.dsv.su.se using the correct user name, password.
	 */
	public DBConnection() {
		try {
			// Information needed for the mySQL-connection
			String url = "atlas.dsv.su.se";
			String username = "emmawil";
			String password = "bur73slang";
			String db = "emmawil";
			// Creates a connection to the database from the DriverManager
			con = DriverManager.getConnection("jdbc:mysql://" + url + "/" + db,
					username, password);
			stmt = con.createStatement();
		} catch (SQLException e) {
			displayErrorMessage("Kunde inte ansluta till databasen - kontrollera internetanslutningen");
		}
	}


	/**
	 * Checks in the device into the system
	 * 
	 * @param device the information for the device that is checking in
	 * @return a control Integer for telling if the check in is successful
	 */
	public int checkIn(DeviceInfo device) {

		int location = -1;
		location = getLocationID(device.getBssid());

		int result = -1;
		PreparedStatement checkIn = null;
		try {
			checkIn = con.prepareStatement(checkInStatement);
			checkIn.setString(1, device.getDeviceID());
			checkIn.setString(2, device.getOwner());
			checkIn.setString(3, device.getIpAddress());
			checkIn.setInt(4, device.getPort());
			checkIn.setTimestamp(5, device.getLastSeen());
			checkIn.setInt(6, location);
			result = checkIn.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return location;
	}


	/**
	 * Gets the locationID for the specified BSSID.
	 * 
	 * @param bssid the BSSID to determine the location
	 */
	private int getLocationID(String bssid) {

		int locationID = -1;
		try {
			PreparedStatement loc = con.prepareStatement(getLocationStatement);
			loc.setString(1, bssid);
			ResultSet rs = loc.executeQuery();
			while (rs.next()) {
				locationID = rs.getInt("locationid");
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return locationID;
	}


	/**
	 * Returns an <code>ArrayList</code> with <code>DeviceInfo</code> all
	 * currently connected devices in the system's database.
	 * 
	 * @return an <code>ArrayList</code> containing <code>DeviceInfo</code> with
	 * all information regarding currently connected devices
	 * @see ArrayList
	 */
	public ArrayList<DeviceInfo> getAllConnectedDevices() {

		// Creates an ArrayList for all locations containing an ArrayList with
		// all devices
		ArrayList<DeviceInfo> allDevices = new ArrayList<DeviceInfo>();
		String bssid = "bssid";
		String ssid = "ssid";
		String deviceID;
		String owner;
		String ip;
		int port;
		int location;

		// creates a PreparedStatement for retrieving all devices
		PreparedStatement getDevices;
		try {
			getDevices = con.prepareStatement(getAllDevicesStatement);
			ResultSet rt = getDevices.executeQuery();
			while (rt.next()) {
				deviceID = rt.getString("deviceid");
				owner = rt.getString("owner");
				ip = rt.getString("ip");
				port = rt.getInt("port");
				location = rt.getInt("location");
				HashMap<String, String> ids = getBssidAndSsid(location);
				// creates a DeviceInfo object from the retrieved data
				DeviceInfo currentDevice = new DeviceInfo(deviceID, owner);
				currentDevice.setBssid(ids.get(bssid));
				currentDevice.setSsid(ids.get(ssid));
				currentDevice.setIpAddress(ip);
				currentDevice.setPort(port);
				currentDevice.setLocation(location);
				// and adds that DeviceInfo object to the allDevices
				// ArrayList
				allDevices.add(currentDevice);
			}
			rt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return allDevices;
	}


	/**
	 * Gets the BSSID and SSID for the specified location.
	 * 
	 * @param location the location that will determine the BSSID and SSID
	 * @return the BSSID and SSID for the location
	 */
	private HashMap<String, String> getBssidAndSsid(int location) {
		HashMap<String, String> idsForLocation = new HashMap<String, String>();
		String bssidKey = "bssid";
		String ssidKey = "ssid";
		String bssidValue = null;
		String ssidValue = null;
		PreparedStatement getIDs;
		try {
			getIDs = con.prepareStatement(getIDsForDevice);
			getIDs.setInt(1, location);
			ResultSet rt = getIDs.executeQuery();
			while (rt.next()) {
				bssidValue = rt.getString("bssid");
				ssidValue = rt.getString("ssid");
			}
			rt.close();
			idsForLocation.put(bssidKey, bssidValue);
			idsForLocation.put(ssidKey, ssidValue);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return idsForLocation;
	}


	/**
	 * Gets and returns an <code>ArrayList</code> that contains another
	 * <code>ArrayList</code> with DeviceInfo for all connected devices
	 * 
	 * @return all locations with their respective connected devices
	 */
	public ArrayList<DeviceList> getAllDevicesForLocation() {
		ArrayList<DeviceList> allLocations = new ArrayList<DeviceList>();
		int location;
		String deviceID;
		String owner;
		String ip;
		int port;
		Timestamp lastSeen;

		ArrayList<Integer> allLocationIDs = new ArrayList<Integer>();
		PreparedStatement getLocations;
		String bssid = "bssid";
		String ssid = "ssid";
		try {
			getLocations = con.prepareStatement(getAllLocationsStatement);
			ResultSet rs = getLocations.executeQuery();
			while (rs.next()) {
				location = rs.getInt("locationid");
				allLocationIDs.add(location);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		PreparedStatement getAllDevices;
		try {
			getAllDevices = con
					.prepareStatement(getAllDevicesForLocationStatement);
			for (Integer currentLocation : allLocationIDs) {
				DeviceList locationDevices = new DeviceList();
				getAllDevices.setInt(1, currentLocation);
				ResultSet rt = getAllDevices.executeQuery();
				while (rt.next()) {
					deviceID = rt.getString("deviceid");
					owner = rt.getString("owner");
					ip = rt.getString("ip");
					port = rt.getInt("port");
					lastSeen = rt.getTimestamp("lastseen");
					HashMap<String, String> ids = getBssidAndSsid(currentLocation);
					// creates a DeviceInfo object from the retrieved data
					DeviceInfo currentDevice = new DeviceInfo(deviceID, owner);
					currentDevice.setBssid(ids.get(bssid));
					currentDevice.setSsid(ids.get(ssid));
					currentDevice.setIpAddress(ip);
					currentDevice.setPort(port);
					currentDevice.setLastSeen(lastSeen);
					currentDevice.setLocation(currentLocation);
					locationDevices.add(currentDevice);
				}
				allLocations.add(locationDevices);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return allLocations;

	}


	/**
	 * Gets and returns all activities for a specific location.
	 * 
	 * @param locationID the locationID for the location
	 * @return all VenueActivities for the specified location
	 */
	public VenueList getActivitiesForVenues(int currentLocation) {
		VenueList allVenuesAndActivities = new VenueList();

		int locationID;
		String venue;
		String activity;
		int minParticipants;
		int maxParticipants;

		try {
			PreparedStatement getActivitiesForLocation = con
					.prepareStatement(getAllVenuesAndActivitiesStatement);
			getActivitiesForLocation.setInt(1, currentLocation);

			ResultSet rs = getActivitiesForLocation.executeQuery();
			while (rs.next()) {
				locationID = rs.getInt("location");
				venue = rs.getString("venuename");
				activity = rs.getString("activitytype");
				minParticipants = rs.getInt("minParticipants");
				maxParticipants = rs.getInt("maxParticipants");
				VenueActivity currentVenueActivity = new VenueActivity(
						locationID, venue, activity, minParticipants,
						maxParticipants);
				allVenuesAndActivities.add(currentVenueActivity);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allVenuesAndActivities;
	}


	/**
	 * Inserts a new Venue that the user has created.
	 * 
	 * @param newVenueForDBInsertion the Venue that the user has entered
	 * information about
	 * @return the auto incremented venueID that the DB creates
	 */
	public int createVenue(Venue newVenueForDBInsertion) {
		int autoIncrementedVenueID = -1;

		try {
			PreparedStatement createVenue = con.prepareStatement(
					createVenueStatement, Statement.RETURN_GENERATED_KEYS);
			createVenue.setString(1, newVenueForDBInsertion.getVenueName());
			createVenue.setString(2, newVenueForDBInsertion.getVenueAddress());
			createVenue.setString(3, newVenueForDBInsertion.getVenueZip());
			createVenue.setString(4, newVenueForDBInsertion.getVenueTown());
			createVenue.setString(5, newVenueForDBInsertion.getVenuePhone());
			createVenue.executeUpdate();

			ResultSet rs = createVenue.getGeneratedKeys();
			while (rs.next()) {
				autoIncrementedVenueID = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return autoIncrementedVenueID;
	}

	// /**
	// * @param location
	// * @return
	// */
	// public int createLocation(Location location) {
	// int result = 0;
	// PreparedStatement createLocation = null;
	// try {
	// createLocation = con.prepareStatement(
	// "INSERT INTO LOCATION(locationname) VALUES (?)",
	// Statement.RETURN_GENERATED_KEYS);
	// createLocation.setString(1, location.getLocationName());
	// result = createLocation.executeUpdate();
	//
	// ResultSet res = createLocation.getGeneratedKeys();
	// while (res.next()) {
	// return res.getInt(1);
	// }
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	// return -1;
	// }

}
